<?php
/*
 * @See Clu_Db_Table_Abstract
 */
class Model_TLeg extends Zend_Db_Table_Abstract
{
	//only specify the table name, everything else inherits
	protected $_name = "tlegs";
	protected $_primary = "id";

	public function findByPrimary($id)
	{
		if (!$id)
            return null;
        else
		    return $this->fetchRow('id = ' . $this->getAdapter()->quote($id, 'INTEGER'));
	}
	
	
	public function insertRaw($data ,$doValidate = false)
	{ 
		if($doValidate)
    	{
    		$form = new Form_Tour();
    		if(!$form->isValid($data))
    			return false;
		}
		
	    //update only those keys
		$keys = array('tid', 'leg_number', 'tleg_name', 'description',
					'article_id','arrival_accommodation_type', 'is_last_leg',
					'transport_type', 'transport_company',
					'duration', 'departure_time', 'arrival_time'
					);
		$tl = clu_array_merge($keys, $data);
		$tl['departure_date']  = string_to_date($data['departure_date']);
		$tl['arrival_date']    = string_to_date($data['arrival_date']);
		$mCity = new place_Model_City();
        $mDistrict = new place_Model_District();
		$tourMetadata = array();
		
    	//departure address
    	if(isset($data['departure_place_name']))
    	{
	        $tourMetadata['departure_place_id'] = $data['departure_place_id'];
			$tourMetadata['departure_place_name'] = $data['departure_place_name'];
    	}
    	if(isset($data['departure_city_id']))
    	{
	    	$cityName = $mCity->getCityName($data['departure_city_id']);
			$tourMetadata['departure_city_id'] = $data['departure_city_id'];
			$tourMetadata['departure_city_name'] = $cityName['city_name'];
    	}
    	if(isset($data['departure_district_id']))
    	{
	        $districtName = $mDistrict->getDistrictName($data['departure_district_id']);
			$tourMetadata['departure_district_id'] = $data['departure_district_id'];
			$tourMetadata['departure_district_name'] = $districtName['district_name'];
    	}
    	
		//arrival address: if belong to
        if(!empty($data['belong_to_ids']))
        {
			$mPlace = new place_Model_Place();
			$placeData = $mPlace->findByPrimary($data['belong_to_ids']);
			$tourMetadata['arrival_place_id'] = $data['belong_to_ids'];
			$tourMetadata['arrival_place_name'] = $data['belong_to_names_submit'];
			if(isset($placeData['city_id']))
			{
		        $cityName = $mCity->getCityName($placeData['city_id']);
				$tourMetadata['arrival_city_id'] = $placeData['city_id'];
				$tourMetadata['arrival_city_name'] = $cityName['city_name'];
			}
			if(isset($placeData['district_id']))
			{
		        $districtName = $mDistrict->getDistrictName($placeData['district_id']);
				$tourMetadata['arrival_district_id'] = $placeData['district_id'];
				$tourMetadata['arrival_district_name'] = $districtName['district_name'];
			}
        } 
		else //arrival address
		{
			if(isset($data['arrival_place_id']) || isset($data['arrival_place_name']))
			{
				$tourMetadata['arrival_place_id'] = $data['arrival_place_id'];
				$tourMetadata['arrival_place_name'] = $data['arrival_place_name'];
			}
			if(isset($data['city_id']))
			{	
				$arrivalCityName = $mCity->getCityName($data['city_id']);
				$tourMetadata['arrival_city_id'] = $data['city_id'];
				$tourMetadata['arrival_city_name'] = $arrivalCityName['city_name'];
			}
			if(isset($data['district_id']))
			{
		        $arrivalDistrictName = $mDistrict->getDistrictName($data['district_id']);
				$tourMetadata['arrival_district_id'] = $data['district_id'];
				$tourMetadata['arrival_district_name'] = $arrivalDistrictName['district_name'];
			}
		}
		
		$tl['metadata'] = serialize($tourMetadata);
//		die_a($tl);
		return $this->insert($tl);

	}
	
	public function saveRaw($leg, $data, $id, $doValidate)
	{
		if((!$leg) && (!$id))
	    	return false;
	    else if(!$leg)
	       	if(!$leg = $this->findByPrimary($id))
	       		return false;

	    if ($doValidate)
	    {
	    	//Validate Data here
	    }
	    //update only those keys
		$keys = array(/*'leg_number'??*/
					'tleg_name',
				    'arrival_accommodation_type', 'is_last_leg',
					'transport_type', 'transport_company',
					'duration', 'departure_time', 'arrival_time'
					);
		$dbData = clu_array_merge($keys, $data);
		$dbData['departure_date'] = string_to_date($data['departure_date']);		              
		$dbData['arrival_date'] = string_to_date($data['arrival_date']);
		
		//updated metadata
		$metadata = $this->getMetadata($leg, $data);
        $dbData['metadata'] = serialize($metadata);
		//die_a($dbData);
		$leg->setFromArray($dbData);
        return $leg->save();
	}
	
	public function getMetadata ($a, $data)
	{
		$metadata =array();
        $dbMeta = unserialize($a->metadata);
		     	
        $mCity = new place_Model_City();
        $mDistrict = new place_Model_District();
        //if tour belong some place:
		//departure address
		if(isset($data['departure_place_name']))
		{
			$metadata['departure_place_id'] = $data['departure_place_id'];
			$metadata['departure_place_name'] = $data['departure_place_name'];
		}
		
		if(isset($data['departure_city_id']))
		{
			$metadata['departure_city_id'] =  $data['departure_city_id'];
			$cityName = $mCity->getCityName($data['departure_city_id']);
	        $metadata['departure_city_name'] = $cityName['city_name'];
		}
		if(isset($data['departure_district_id']))
		{
	        $metadata['departure_district_id'] = $data['departure_district_id'];
	        $districtName = $mDistrict->getDistrictName($data['departure_district_id']);
	        $metadata['departure_district_name'] = $districtName['district_name'];
		}
	    
	    //if belong to => city_id, district_id
		if( !empty($data['belong_to_ids']))
		{
	        //arrival address
	        $metadata['arrival_place_id'] = $data['belong_to_ids'];
			$metadata['arrival_place_name'] = $data['belong_to_names_submit'];
			$mPlace = new place_Model_Place();
			$placeMetadata = $mPlace->findByPrimary($data['belong_to_ids']);
			if(isset($data['city_id']))
			{
				$metadata['arrival_city_id'] = $placeMetadata['city_id'];
				$cityName = $mCity->getCityName($placeMetadata['city_id']);
		        $metadata['arrival_city_name'] = $cityName['city_name'];
			}
			if(isset($data['district_id']))
			{
		        $metadata['arrival_district_id'] = $placeMetadata['district_id'];
		        $districtName = $mDistrict->getDistrictName($placeMetadata['district_id']);
		        $metadata['arrival_district_name'] = $districtName['district_name'];
			}
		}
		else //arrival address
		{ 
			/*
			$metadata['arrival_place_id'] = $dbMeta['arrival_place_id'];
			$metadata['arrival_place_name'] = $dbMeta['arrival_place_name'];
			if(isset($dbMeta['arrival_city_id']) || isset($dbMeta['arrival_city_name']))
			{
				$metadata['arrival_city_id'] = $dbMeta['arrival_city_id'];
				$metadata['arrival_city_name'] = $dbMeta['arrival_city_name'];
			}
			if(isset($dbMeta['arrival_district_id']) || isset($dbMeta['arrival_district_name']))
			{
				$metadata['arrival_district_id'] = $dbMeta['arrival_district_id'];
				$metadata['arrival_district_name'] = $dbMeta['arrival_district_name'];
			}
			*/
			/*
			//departure address
			if(isset($data['departure_place_name']))
			{
				$metadata['departure_place_id'] = $data['departure_place_id'];
				$metadata['departure_place_name'] = $data['departure_place_name'];
			}
			else if(isset($dbMeta['departure_place_name']))
			{
				$metadata['departure_place_id'] = $dbMeta['departure_place_id'];
				$metadata['departure_place_name'] = $dbMeta['departure_place_name'];
			}
	    	if(isset($data['departure_city_id']))
	    	{
	        	$cityName = $mCity->getCityName($data['departure_city_id']);
	        	$metadata['departure_city_id'] = $data['departure_city_id'];
		        $metadata['departure_city_name'] = $cityName['city_name'];
	    	}
	    	else if (isset($dbMeta['departure_city_id']))
	    	{
	    		$metadata['departure_city_id'] = $dbMeta['departure_city_id'];
	    		$metadata['departure_city_name'] = $dbMeta['departure_city_name'];
	    	}
	    		
	    	if(isset($data['departure_district_id']))
	    	{
		        $districtName = $mDistrict->getDistrictName($data['departure_district_id']);
		        $metadata['departure_district_id'] = $data['departure_district_id'];
		        $metadata['departure_district_name'] = $districtName['district_name'];
	    	}
	    	else if (isset($dbMeta['departure_district_id']))
	    	{
	    		$metadata['departure_district_id'] = $dbMeta['departure_district_id'];
	    		$metadata['departure_district_name'] = $dbMeta['departure_district_name'];
	    	}
	    	*/
	       		
	       	// arrival address
	       	if(isset($data['arrival_place_name']))
			{
				$metadata['arrival_place_id'] = $dbMeta['arrival_place_id'];
				$metadata['arrival_place_name'] = $data['arrival_place_name'];
			}
			else if(isset($dbMeta['arrival_place_name']))
			{
				$metadata['arrival_place_id'] = $dbMeta['arrival_place_id'];
				$metadata['arrival_place_name'] = $dbMeta['arrival_place_name'];
			}
	    	if(isset($data['city_id']))
	    	{
	        	$cityName = $mCity->getCityName($data['city_id']);
	        	$metadata['arrival_city_id'] = $data['city_id'];
		        $metadata['arrival_city_name'] = $cityName['city_name'];
	    	}
	    	else if (isset($dbMeta['arrival_city_id']))
	    	{
	    		$metadata['arrival_city_id'] = $dbMeta['arrival_city_id'];
	    		$metadata['arrival_city_name'] = $dbMeta['arrival_city_name'];
	    	}
	    		
	    	if(isset($data['district_id']))
	    	{
		        $districtName = $mDistrict->getDistrictName($data['district_id']);
		        $metadata['arrival_district_id'] = $data['district_id'];
		        $metadata['arrival_district_name'] = $districtName['district_name'];
	    	}
	    	else if (isset($dbMeta['arrival_district_id']))
	    	{
	    		$metadata['arrival_district_id'] = $dbMeta['arrival_district_id'];
	    		$metadata['arrival_district_name'] = $dbMeta['arrival_district_name'];
	    	}
	    	
		}
    	
//    	die_a($metadata);
        return $metadata;
	}    
	
	/**
	 * Get the tour leg 
	 * @param: getLegArticle: true or false
	 * @mode: array or object
	 * @return 
	 * 	will be something like	
	 * 		$ret[1] = data of leg number 1 (1st leg of tour $tid), in array|object (depending on $mode) format
	 * 
	 *  note that it's a little different when returning leg info in $ret[$leg_num]['leg'] in stead of usual $
	 */
	public function getLegsByTourId($tid, $getLegArticle = false, $mode = 'array')
	{
		$db = $this->getAdapter();
        $select = $this->select()->where('tid = ?', (int)$db->quote($tid, 'INT'))
        						 ->from(array('t' => 'tlegs'))
        						 ->order('t.leg_number ASC');
		$legs = $this->fetchAll($select);
//		$legs = $this->fetchAll('tid = ' . $db->quote($tid, 'INTEGER') /*TODO . ' ORDER BY leg_number ASC' */);
		//$legs = $legs->toArray();
		
		$ret = array();
		if (count($legs) == 0)
		{
			return null;
		}
		if ($getLegArticle)
		{
			//require_once APP_PATH.'/modules/article/Model/Article.php';
			$articleModel = new article_Model_Service();
		}	
		foreach ($legs as $leg)
		{
			$leg_num = $leg->leg_number;
			//leg article data
			if ($mode === 'array')
				$ret[$leg_num]['leg'] = $leg->toArray();
			elseif ($mode === 'object')
				$ret[$leg_num]['leg'] = $leg;
			if ($getLegArticle)
				$ret[$leg_num]['article'] = $articleModel->findByPrimary($leg->article_id);
			//leg row data
			
		}
		return $ret;
	}
	
	/**
	 * Get the tour data: including all the fields in the 'tours' table
	 * Full mode will get article data & legs data as well
	 * @param: getArticle: true or false
	 */
	public function getLegData($lid, $getLegArticle)
	{
		if ($getLegArticle)
		{
			$articleModel = new article_Model_Service();
			if(count($lid) > 1)
			{
				$ret = array();
				foreach($lid as $id)
					$ret[] = $articleModel->getArticleData($id);//$leg['article_id']);
			}
			else
			{
				if ($leg = $this->findByPrimary($lid))
				{
					$ret = $leg->toArray();
					$ret['article'] = $articleModel->findByPrimary($leg['article_id']);
				}
		 		else 
		 			return null;
			}
		}
		return $ret;
	}
	
	public function search($data)
	{	
		$title 			= isset($data['title'])       ? $data['title']:null;
   		$keywords 		= isset($data['keywords'])    ? $data['keywords']:null;
		$description 	= isset($data['description']) ? $data['description'] : null;
		$category 		= isset($data['category'])    ? $data['category'] : null; 
		$status 		= isset($data['status'])      ? $data['status']:null;
     	$frequency		= isset($data['frequency'])   ? $data['frequency']:null;
     	$searchTime		= isset($data['search_time']) ? $data['search_time'] :null;
     	$calendarType	= isset($data['calendarType']) ? $data['calendarType'] :null;
     	
//     	$startDate		= isset($data['startDate'])  ? $this->stringToTime($data['startDate']) : null;
//		$finishDate		= isset($data['finishDate']) ? $this->stringToTime($data['finishDate']) : null;
		//date info
		if (!empty($data['startDate']) && !empty($data['finishDate']))
		{
    		list($startDate, $startMonth, $startYear ) = split('[/.-]', $data['startDate']);
    		list($finishDate, $finishMonth, $finishYear ) = split('[/.-]', $data['finishDate']);
		}		
		//select SQL query
    	$db = $this->getAdapter();
		$select = $db->select();
	
//				     ->distinct();
		
		// Add a FROM clause
        $select->from( array('e' => 'events'));
        
        if($title)
            $select->where('e.title LIKE ?',$db->quoteinto("%$title%","STRING"));
            
        if($keywords)
            $select->where('e.keywords LIKE ?',$db->quoteinto("%$keywords%","STRING"));
        
        if($description)
            $select->where('e.description LIKE ?',$db->quoteinto("%$description%","STRING"));
            
        if($status && $status != 'all')
    		$select->where('e.status = ?', $db->quoteinto($status,'STRING'));
    	
	 	if($category)
        {
            foreach($category as $c)
                $select->orwhere($db->quoteinto("find_in_set(?,e.category)", $c));
        }

        //frequency & time search
    	$a =getdate();
    	$currentTime = $a['mday'] + $a['mon'] * 35 + $a['year'] * 500;

    		
		/* 
			frequencyData $f has the following data
			$f['weekly'] = array ('2', '6'); //monday-friday
			$f['monthly'] = array('12', '24'); //all monthly events from the 12th to the 24th;
			$f['yearly'] = array ('12/05', '15/05') ; //all yearly events from 12May to 15May
			$f['once'] = array ('12/05/2009:15:00', '12/05/2009:17:00');//all 'once' events from 15:00 to 17:00 on the date
			$f['daily'] = array ('15:00', '16:00');// all daily events from 15:00 -16:00 
 		*/
		//now find $frequencyData for each frequency specified;
		
		$frequencyData = array();
		foreach ($frequency as $f)
		{
			$tmp = array();
			if ($f == 'once')
			{
				$tmp[0] = (int)$startYear*500 + (int)$startMonth*35 + (int)$startDate;
				$tmp[1] = (int)$finishYear*500 + (int)$finishMonth*35 + (int)$finishDate;
			}				
			elseif ($f == 'monthly')
			{
				//only the date
				$tmp[0] = $startDate;
				$tmp[1] = $finishDate;
			}
			elseif ($f == 'yearly')
			{
				//only the date + month
				$tmp[0] = (int)$startMonth*35 + (int)$startDate;
				$tmp[1] = (int)$finishMonth*35 + (int)$finishDate;
			}
			elseif ($f == 'hourly')
			{
				//TODO: add the hour to the search form
			}
			elseif ($f == 'weekly')
			{
				/*TODO: if user doesn't specify the days of the week -> error
				$s		= getdate(strtotime($data['startDate']));
        		$tmp[0] 		= $s['wday'] +1;
        		$f		= getdate(strtotime($data['finishDate']));
        		$tmp[1] = $f['wday'] +1;
				*/
				$tmp[0] = $data['fromDay'];
				$tmp[1] = $data['toDay'];
			}
			$frequencyData[$f] = $tmp;
		}	
    	
		$timeSql = " 0 ";
		foreach ($frequency as $f)
		{
			$q = sprintf(" e.frequency = '%s'",$db->quoteinto($f,'STRING'));
			$q .= " AND ((e.start_%s >= ". $db->quoteinto($frequencyData[$f][0],'INT');
			$q .= " AND e.start_%s <= ". $db->quoteinto($frequencyData[$f][1],'INT') . ")";
			
			$q .= " OR (e.finish_%s >= ". $db->quoteinto($frequencyData[$f][0],'INT');
			$q .= " AND e.finish_%s <= ". $db->quoteinto($frequencyData[$f][1],'INT') . "))";
			
			$criteria = '';
			if ($f == 'daily')
				$criteria = 'time';
			elseif ($f == 'once' || $f == 'yearly' || $f == 'monthly')
				$criteria = 'date';
			elseif($f == 'weekly')
				$criteria = 'day';			
			$timeSql .= " OR (". sprintf($q, $criteria,$criteria,$criteria,$criteria). ") ";
		}
		$select->where($timeSql);
		//die_a($select->__toString());				
    	//    	if($frequency)
//    		$select->where('e.frequency = ?', $db->quoteinto($frequency,'STRING'));
//    	die($select);
        return $select;
	}
	
	public function deleteByPrimary($idArray, $atomic = true)
	{
        $aModel = new article_Model_Service();
        foreach ($idArray as $eventId)
        {
                $tmp = $this->findByPrimary($eventId);
                $articleIds[] = $tmp->article_id;
        }        
        $db = $this->getAdapter();
        if ($atomic)
	        $db->beginTransaction(); //@NOTE: no effect with MyISAM
        try
        {
            $sql = "DELETE FROM events WHERE id IN (". $db->quote($idArray, 'INTEGER') .")" ;
           	$db->query($sql);
           	$sql1 = "DELETE FROM articles WHERE article_id IN (". $db->quote($articleIds, 'INTEGER') .")" ;
           	$db->query($sql1);
           	if ($atomic)
	            $db->commit();
            return true;
    	}
        catch (Exception $e)
        {
            if ($atomic)
	            $db->rollBack();
            return false;
        }
	}
	
	public function bulkUpdateStatus($idArray, $data)
	{
		$idList = implode (',', $idArray);
		$db = $this->getAdapter();
        $db->beginTransaction();
        try {
            $sql =  "UPDATE events SET status = ".$db->quote($data['status'],'STRING')." WHERE id IN (".$idList.")" ;
            $db->query($sql);
            $db->commit();
			return true;
        }    
        catch (Exception $e)
        {
            $db->rollBack();
            return false;
        }   	
	}
	


	public function transformToFormArray($leg)
	{
		$keys = array('tid','leg_number', 'tleg_name',
				     'arrival_accommodation_type', 'is_last_leg',
					 'transport_type', 'transport_company',
					  'duration', 'metadata',
					'departure_time', 'arrival_time'
					);
		$ret = clu_array_merge($keys, $leg);
    	$metadata = unserialize($ret['metadata']);
		$ret['arrival_place_id'] = $metadata['arrival_place_id'];
		$ret['arrival_place_name'] = $metadata['arrival_place_name'];
		if(isset($metadata['arrival_city_id']))
		{
			$ret['city_id'] = $metadata['arrival_city_id'];
			$ret['arrival_city_name'] = $metadata['arrival_city_name'];
		}
		if(isset($metadata['arrival_district_id']))
		{
			$ret['district_id'] = $metadata['arrival_district_id'];
			$ret['arrival_district_name'] = $metadata['arrival_district_name'];
		}
		
		$ret['departure_place_id'] = $metadata['departure_place_id'];
		$ret['departure_place_name'] = $metadata['departure_place_name'];
		$ret['departure_city_id'] = $metadata['departure_city_id'];
		$ret['departure_city_name'] = $metadata['departure_city_name'];
		$ret['departure_district_id'] = $metadata['departure_district_id'];
		$ret['departure_district_name'] = $metadata['departure_district_name'];
		
		//extra data that needs transformation
		$ret['description'] 	= $leg['article']['article_body']; //TODO: body or filtered_body
		$ret['departure_date'] = date_to_string($leg['departure_date']);
		$ret['arrival_date'] = date_to_string($leg['arrival_date']);
		
		return $ret;
	}
	
}
?>